package cn.school.listener;

import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.*;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.util.SqlBasicVisitor;
import org.apache.calcite.sql.validate.SqlConformanceEnum;

public class CustomSqlParser {
    public static void main(String[] args) throws SqlParseException {
        String sqlStr = "SELECT\n" +
                "\tsu.dept_id `deptId`,\n" +
                "\tsu.user_id,\n" +
                "\tsr.role_id,\n" +
                "\tsu.user_name,\n" +
                "\tsd.dept_name,\n" +
                "\tsr.role_name,\n" +
                "(select 1 from table1 t where t.id=sd.id) id "+
                "FROM\n" +
                "\tsys_user AS su\n" +
                "JOIN sys_dept sd ON su.dept_id = sd.dept_id\n" +
                "JOIN sys_user_role sur ON sur.user_id = su.user_id\n" +
                "JOIN sys_role sr ON sur.role_id = sr.role_id\n" +
                "WHERE\n" +
                "\tsd.dept_name = '研发部门'\n" +
                "\tand su.user_name = 'admin'\n" +
                "\tand su.dept_id = 103\n" +
                "\tor sr.role_name = '超级管理员'\n" +
                "ORDER BY\n" +
                "\tsd.create_time DESC LIMIT 1,2";
        SqlParser.Config mysqlConfig = SqlParser.configBuilder()
                .setLex(Lex.MYSQL)
                .setCaseSensitive(false)//大小写敏感
                .setQuoting(Quoting.BACK_TICK)
                .setQuotedCasing(Casing.TO_LOWER)
                .setUnquotedCasing(Casing.TO_LOWER)
                .setConformance(SqlConformanceEnum.MYSQL_5)
                .build();
        SqlNode sqlNode = SqlParser.create(sqlStr, mysqlConfig).parseQuery();

        sqlNode.accept(new SqlBasicVisitor<String>() {
            public String visit(SqlCall call) {
                if (call.getKind().equals(SqlKind.SELECT)) {
                    SqlSelect select = (SqlSelect) call;
                    SqlNode fetchNode = select.getFetch();
                    System.out.println();
                    System.out.println("--------------查询列名----------------------------------------");
                    select.getSelectList().forEach(colum -> {
                        if (SqlKind.AS.equals(colum.getKind())) {
                            SqlBasicCall basicCall = (SqlBasicCall) colum;
                            System.out.println("{} as {}" +  basicCall.getOperandList().get(0).toString() +" __"+basicCall.getOperandList().get(1).toString());
                        } else if (SqlKind.IDENTIFIER.equals(colum.getKind())) {
                            System.out.println(colum.toString());
                        }
                    });
                    System.out.println("--------------From Table Info----------------------------------------");
                    select.getFrom().accept(new SqlBasicVisitor<String>() {
                        public String visit(SqlCall call) {
                            if (call.getKind().equals(SqlKind.JOIN)) {
                                SqlJoin join = (SqlJoin) call;
                                System.out.println("join.getRight:{}," + join.getRight().toString());
                                System.out.println("join.getCondition{}" +join.getCondition().toString());
                                if (!join.getLeft().getKind().equals(SqlKind.JOIN)) {
                                    System.out.println("join.getLeft:{}"+ join.getLeft().toString());
                                }
                            }else{

                            }
                            return call.getOperator().acceptCall(this, call);
                        }
                    });
                    System.out.println("--------------Where  Info-----------------------------------------");
                    select.getWhere().accept(new SqlBasicVisitor<String>() {
                        public String visit(SqlCall call) {
                            if (call.getKind().equals(SqlKind.AND) || call.getKind().equals(SqlKind.OR)) {
                                SqlBasicCall sql = (SqlBasicCall) call;
                                SqlBasicCall left = (SqlBasicCall) sql.getOperandList().get(0);
                                SqlBasicCall right = (SqlBasicCall) sql.getOperandList().get(1);
                                System.out.println("kind:{}" +sql.getKind());
                                System.out.println("right:{}"+ right);
                                if (!left.getKind().equals(SqlKind.AND) && !left.getKind().equals(SqlKind.OR)) {
                                    System.out.println("left:{}" + left);
                                }
                            }
                            return call.getOperator().acceptCall(this, call);
                        }
                    });
                    System.out.println("--------------增加查询条件----------------------------------------");
                    try {
                        SqlNode condition = SqlParser.create("1=1").parseExpression();
                        SqlNode where = SqlUtil.andExpressions(select.getWhere(),condition);
                        select.setWhere(where);
                    } catch (SqlParseException e) {
                        throw new RuntimeException(e);
                    }
                    System.out.println("语句:{}" + select);
                }
                return call.getOperator().acceptCall(this, call);
            }
        });

    }


}
